package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.heaven.entity.CourseCode;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 课程教务代码Dao类
 * Created by chenxin on 2014/12/9.
 */
@Repository
public class CourseCodeDao extends BaseDao {
    /**
     * 判断教务代码是否存在
     * @param courseCode 教务代码
     * @return boolean
     *
     * @author cx
     * @since 2014-12-09
     */
    public boolean isExist(CourseCode courseCode) {
        String sql = "SELECT COUNT(id) FROM t_course_code WHERE sCode=:code";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("code", courseCode.getCode());

        log.debug("判断教务代码是否存在");
        return namedParameterJdbcTemplate.queryForObject(sql, sps, Integer.class) > 0;
    }

    /**
     * 新增课程教务代码
     * @param courseCode 教务代码
     * @return boolean
     *
     * @author cx
     * @since 2014-12-09
     */
    public boolean save(CourseCode courseCode) {
        String sql = "INSERT INTO t_course_code VALUES(NULL,:courseId,:code)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        SqlParameterSource sps = new MapSqlParameterSource().addValue("courseId", courseCode.getCourseId())
                .addValue("code", courseCode.getCode());

        int result = namedParameterJdbcTemplate.update(sql, sps, keyHolder);
        if (result == 1) {
            log.debug("新增课程教务代码成功：result == 1");
            //获取新增后的代码ID
            courseCode.setId(keyHolder.getKey().longValue());
            return true;
        }

        log.debug("保存课程教务代码失败");
        log.warn("保存课程教务代码失败:result->" + result);
        return false;
    }

    /**
     * 更新课程教务代码
     * @param courseCode 课程教务代码
     * @return boolean
     *
     * @author cx
     * @since 2014-12-09
     */
    public boolean update(CourseCode courseCode) {
        String sql = "UPDATE t_course_code SET sCode=:code WHERE id=:id";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("code", courseCode.getCode()).addValue("id", courseCode.getId());

        log.debug("更新课程教务代码");
        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 删除课程教务代码
     * @param id 教务代码ID
     * @return boolean
     *
     * @author cx
     * @since 2014-12-09
     */
    public boolean delete(final long id) {
        String sql = "DELETE FROM t_course_code WHERE id=:id";

        log.debug("删除课程教务代码");
        return namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource().addValue("id", id)) == 1;
    }

    /**
     * 获取课程教务代码
     * @param courseId 课程ID
     * @return List
     *
     * @author cx
     * @since 2014-12-09
     */
    public List<CourseCode> getAll(final long courseId) {
        String sql = "SELECT * FROM t_course_code WHERE iCourseId=:courseId";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("courseId", courseId);

        log.debug("获取课程教务代码");
        return namedParameterJdbcTemplate.query(sql, sps, new RowMapper<CourseCode>() {
            @Override
            public CourseCode mapRow(ResultSet rs, int rowNum) throws SQLException {
                CourseCode entity = new CourseCode();

                entity.setId(rs.getLong("id"));
                entity.setCourseId(rs.getLong("iCourseId"));
                entity.setCode(rs.getString("sCode"));

                return entity;
            }
        });
    }

    /**
     * 获取学生所属的课程列表
     * @param studentNum 学号
     * @return List
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public List<Long> getListForStudent(String studentNum) {
        String sql = "SELECT iCourseId FROM t_course_code c LEFT JOIN t_student_course s ON c.sCode=" +
                "s.sCourseCode WHERE s.sStudentNum=:studentNum";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("studentNum", studentNum),
                new RowMapper<Long>() {
                    @Override
                    public Long mapRow(ResultSet resultSet, int i) throws SQLException {

                        return resultSet.getLong("iCourseId");
                    }
                });
    }

    /**
     * 查询课程代码属于课程
     * @param code 代码
     * @param courseId 课程ID
     * @return bool
     *
     * @author wyh
     * @since 2015-03-26
     */
    public boolean checkCodeInCourse(String code, long courseId){
        String sql = "SELECT COUNT(*) FROM t_course_code WHERE iCourseId=:courseId AND sCode=:code";

        log.debug("返回 课程代码是否属于课程");
        return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("courseId", courseId).addValue("code", code), Integer.class) > 0;
    }

    /**
     * 查询课程的课程代码集合
     * @param courseId 课程id
     * @return List
     *
     * @author wyh
     * @since 2015-03-31
     */
    public List<String> getCodesById(long courseId){
        String sql = "SELECT sCode FROM t_course_code WHERE iCourseId=:courseId";

        log.debug("返回课程的课程代码集合");
        return namedParameterJdbcTemplate.queryForList(sql, new MapSqlParameterSource().addValue("courseId", courseId), String.class);
    }
}
